package com.project_doctor.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.project_doctor.pojo.Contract;
import com.project_doctor.pojo.dto.ContractInfoDTO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.project_doctor.pojo.dto.ContractQueryDTO;
import com.project_doctor.pojo.vo.ContractDetailVO;
import com.project_doctor.pojo.vo.ContractVO;
import com.project_doctor.pojo.vo.DetailsVO;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Select;

/**
* @author xixif
* @description 针对表【t_contract(签约服务记录表)】的数据库操作Mapper
* @createDate 2025-06-10 18:51:42
* @Entity com.project_doctor.pojo.Contract
*/
public interface ContractMapper extends BaseMapper<Contract> {

    List<ContractInfoDTO> selectContractInfoByResidentId(@Param("residentId") Integer residentId);

    List<ContractInfoDTO> selectContractInfoByResidentIdSimple(@Param("residentId") Integer residentId);

    @Select("SELECT t.name as teamName, " +
            "DATE(c.apply_time) as signDate, " +
            "COUNT(*) as signCount " +
            "FROM t_contract c " +
            "LEFT JOIN t_team t ON c.team_id = t.id " +
            "WHERE c.apply_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) " +
            "AND c.apply_time < CURDATE() + INTERVAL 1 DAY " +
            "GROUP BY t.name, DATE(c.apply_time) " +
            "ORDER BY t.name, signDate")
    List<Map<String, Object>> selectWeeklyContractStats();

    @Select("SELECT COUNT(*) as total FROM t_contract")
    Integer getTotalContractCount();

    @Select("SELECT DATE_SUB(CURDATE(), INTERVAL #{day} DAY) as date, " +
            "COUNT(*) as count " +
            "FROM t_contract " +
            "WHERE DATE(apply_time) = DATE_SUB(CURDATE(), INTERVAL #{day} DAY)")
    Map<String, Object> getContractCountByDay(@Param("day") int day);

    List<ContractVO> selectAllContract(Page<ContractVO> page, @Param("queryDTO") ContractQueryDTO queryDTO);

    @Select("SELECT " +
            "C.id, " +
            "R.name, " +
            "C.resident_id as residentId, " +
            "R.id_card, " +
            "R.phone, " +
            "R.age, " +
            "R.address as address, " +
            "S.name as statusName, " +
            "S.id as status, " +
            "T.name as teamName, " +
            "T.id as team, " +
            "D.name as doctorName, " +
            "D.id as doctor, " +
            "P.name as packageName, " +
            "P.id as packageId, " +
            "C.fee as fee, " +
            "C.apply_time, " +
            "C.effective_date, " +
            "C.expire_date, " +
            "C.sign_cycle as signCycle, " +
            "A.name as tag, " +
            "C.remark as remark, " +
            "R.gender as gender, " +
            "I.name as instName, " +
            "I.id as inst, " +
            "C.type as type, " +
            "R.birthdate as birthdate " +
            "FROM t_contract C " +
            "LEFT JOIN t_resident R ON R.id = C.resident_id " +
            "LEFT JOIN t_status S ON C.contract_status = S.id " +
            "LEFT JOIN t_team T ON T.id = C.team_id " +
            "LEFT JOIN t_package P ON P.id = C.package_id " +
            "LEFT JOIN t_doctor D ON D.id = C.doctor_id " +
            "LEFT JOIN t_tag A ON R.id = A.ref_id " +
            "LEFT JOIN t_institution I ON I.id = C.inst_id " +
            "WHERE C.id = #{id}")
    List<ContractDetailVO> selectAllContractDetail(Integer id);

    @Select("SELECT " +
            "c.id as contractId, " +
            "r.name as residentName, " +
            "r.id_card as idCard, " +
            "r.phone as phone, " +
            "s.name as status, " +
            "p.name as packageName, " +
            "sv.name as serviceName, " +
            "d.name as doctorName, " +
            "i.name as institutionName, " +
            "t.name as teamName, " +
            "c.apply_time as applyTime " +
            "FROM t_contract c " +
            "LEFT JOIN t_resident r ON c.resident_id = r.id " +
            "LEFT JOIN t_status s ON c.service_status = s.id " +
            "LEFT JOIN t_package p ON c.package_id = p.id " +
            "LEFT JOIN t_doctor d ON c.doctor_id = d.id " +
            "LEFT JOIN t_service sv ON c.service_id = sv.id " +
            "LEFT JOIN t_institution i ON c.inst_id = i.id " +
            "LEFT JOIN t_team t ON c.team_id = t.id " +
            "${ew.customSqlSegment}")
    Page<ContractVO> selectContractVoList(Page<ContractVO> page,@Param(Constants.WRAPPER) QueryWrapper<Contract> wrapper);

    @Select("SELECT " +
            "  /* 居民信息字段 */" +
            "  r.id AS residentId, " +
            "  r.name AS residentName, " +
            "  r.id_card AS idCard, " +
            "  r.gender AS gender, " +
            "  r.age AS age, " +
            "  r.birthdate AS birthDate, " +
            "  r.phone AS phone, " +
            "  r.address AS address, " +
            "  /* 服务信息字段 */" +
            "  c.id AS contractId, " +
            "  s.name AS status, " +
            "  i.name AS institutionName, " +
            "  i.id AS institutionId, " +
            "  tm.name AS teamName, " +
            "  tm.id AS teamId, " +
            "  d.name AS doctorName, " +
            "  d.id AS doctorId, " +
            "  p.name AS packageName, " +
            "  p.id AS packageId, " +
            "  sv.name AS serviceName, " +
            "  sv.id AS serviceId, " +
            "  r.source AS serviceSource, " +
            "  a.name AS serviceAddress, " +
            "  a.id AS addressId, " +
            "  c.service_time AS appointmentTime, " +
            "  c.apply_time AS applyTime, " +
            "  c.audit_time AS auditTime, " +
            "  c.audit_name AS auditName, " +
            "  c.service_remark AS serviceRemark, " +
            "  c.rejection_reason AS rejectionReason, " +
            "  c.cancellation_reason AS cancellationReason, " +
            "  c.cancellation_time AS cancellationTime, " +
            "  c.completion_time AS completionTime, " +
            "  h.blood_pressure AS bloodPressure, " +
            "  h.fasting_blood_glucose AS fastingBloodGlucose, " +
            "  h.heart_rate AS heartRate, " +
            "  h.condition_description AS conditionDescription, " +
            "  h.health_guidance AS healthGuidance " +
            "FROM t_contract c " +
            "JOIN t_resident r ON c.resident_id = r.id " +
            "LEFT JOIN t_status s ON c.service_status = s.id " +
            "LEFT JOIN t_institution i ON c.inst_id = i.id " +
            "LEFT JOIN t_team tm ON c.team_id = tm.id " +  // 避免与标签表t重名，改用别名tm
            "LEFT JOIN t_doctor d ON c.doctor_id = d.id " +
            "LEFT JOIN t_package p ON c.package_id = p.id " +
            "LEFT JOIN t_service sv ON c.service_id = sv.id " +
            "LEFT JOIN t_address a ON c.address_id = a.id " +
            "LEFT JOIN t_health h ON c.resident_id = h.resident_id " +
            "WHERE c.id = #{contractId}")
    DetailsVO selectContractDetail(Integer contractId);
}




